﻿Namespace Istatistik
	Friend Module Functions

		Function MYOTopluOdeme(ByVal Process As Token, ByVal Tarih1 As Date, ByVal Tarih2 As Date) As Recordset
			Q("select oe.hesapno, hp.hesapadi, count(*) as adet,sum(borc)-sum(alacak) as toplam")
			Q("from muhasebe..mahsup oe inner join muhasebe..hesplan hp on oe.hesapno = hp.hesapno")
			Q("where	oe.hesapno in ('6000313', '6000309','6000314','6010313', '6010309','6010314')")
			Q("and oe.tarih between :tarih1 and :tarih2")
			Q("group by oe.hesapno, hp.hesapadi")
			Q("order by oe.hesapno, hp.hesapadi")
			Return Process.OpenSQL("*", Tarih1, Tarih2)
		End Function

		Function BordroBasamakDagilim(ByVal Process As Token, ByVal Bordro As String, ByVal Tarih1 As Date, ByVal Tarih2 As Date) As Recordset
			Q("select bordrotar, pek, count(*) as adet from (")
			Q("select bordrotar, kimlik, sum(pek/pogs * 30) as pek")
			Q("from shesk")
			Q("where bordrotar between :t1 and :t2 and bordro = :b")
			Q("and pogs > 0")
			Q("group by bordrotar, kimlik) t")
			Q("group by bordrotar, pek")
			Q("order by 1, 2")
			Return Process.OpenSQL("*", Tarih1, Tarih2, Bordro)
		End Function

		Function BasamakListesi(ByVal Process As Token, ByVal Tarih As Date) As Recordset
			Return Process.OpenSQL("select *, 9999999999999 as basamak21 from sbasamak where genelgetar <= :tarih order by genelgetar desc", Tarih)
		End Function

		Function Bilgi01(ByVal Process As Token, ByVal Tarih As Date, ByVal Yasa As Integer, ByVal Statu As Integer) As Recordset
			Q("select	fkod,")
			Q("count(distinct case when sube = 1 then isyerino else null end)  as isyeri1,")
			Q("sum(case when sube = 1 and cnt = 1 and uyruk = 'kktc' then 1 else 0 end) as kktc1,")
			Q("sum(case when sube = 1 and cnt = 1 and uyruk = 'calisma' then 1 else 0 end) as calisma1,")
			Q("count(distinct case when sube = 2 then isyerino else null end)  as isyeri2, ")
			Q("sum(case when sube = 2 and cnt = 1 and uyruk = 'kktc' then 1 else 0 end) as kktc2,")
			Q("sum(case when sube = 2 and cnt = 1 and uyruk = 'calisma' then 1 else 0 end) as calisma2,")
			Q("count(distinct case when sube = 3 then isyerino else null end)  as isyeri3, ")
			Q("sum(case when sube = 3 and cnt = 1 and uyruk = 'kktc' then 1 else 0 end) as kktc3,")
			Q("sum(case when sube = 3 and cnt = 1 and uyruk = 'calisma' then 1 else 0 end) as calisma3,")
			Q("count(distinct case when sube = 4 then isyerino else null end)  as isyeri4, ")
			Q("sum(case when sube = 4 and cnt = 1 and uyruk = 'kktc' then 1 else 0 end) as kktc4,")
			Q("sum(case when sube = 4 and cnt = 1 and uyruk = 'calisma' then 1 else 0 end) as calisma4,")
			Q("count(distinct case when sube = 5 then isyerino else null end)  as isyeri5, ")
			Q("sum(case when sube = 5 and cnt = 1 and uyruk = 'kktc' then 1 else 0 end) as kktc5,")
			Q("sum(case when sube = 5 and cnt = 1 and uyruk = 'calisma' then 1 else 0 end) as calisma5,")
			Q("count(distinct case when sube = 6 then isyerino else null end)  as isyeri6, ")
			Q("sum(case when sube = 6 and cnt = 1 and uyruk = 'kktc' then 1 else 0 end) as kktc6,")
			Q("sum(case when sube = 6 and cnt = 1 and uyruk = 'calisma' then 1 else 0 end) as calisma6,")
			Q("count(distinct isyerino) as isyeri, ")
			Q("sum(case when cnt = 1 and uyruk = 'kktc' then 1 else 0 end) as kktc,")
			Q("sum(case when cnt = 1 and uyruk = 'calisma' then 1 else 0 end) as calisma,")
			Q("sum(cnt) as toplam ")
			Q("from	(")
			Q("select	distinct h.repsube as sube, substring(i.sicilno,4,2) as fkod, i.isyerino, g.sno, case when g.uyr = '01.03.001' then 'kktc' else 'calisma' end as uyruk,")
			Q("case when ((@Yasa=0) or (@Yasa=1 and g.sno < 99999999) or (@Yasa=2 and g.sno > 99999999)) then 1 else 0 end as cnt")
			Q("from	shesk h inner join isyerigenel i on i.isyerino = h.isyerino and i.repsube = h.repsube inner join sgenel g on h.sno = g.sno and h.repsube = g.repsube")
			Q("where	h.bordrotar = @Tarih")
			Q("and		((@Statu=0) or (@Statu=1 and h.bordro in ('H','H1','J','J1')) or (@Statu=2 and h.bordro not in ('H','H1','J','J1')))")
			Q(") t")
			Q("group by fkod")
			Q("order by fkod")
			Return Process.OpenSQL("*", Yasa, Tarih, Statu)
		End Function

		Sub ParametreEkle(ByVal Process As Token, ByVal Tur As String, ByVal Deger As String)
			Process.Save("parametre", "grup,deger,ad", Tur, Process.OpenSQL("select isnull(max(convert(int,Deger)),0)+1 from Parametre where Grup = @Tur", Tur)(0), Deger)
		End Sub

		Sub ParametreDegistir(ByVal Process As Token, ByVal Kod As Integer, ByVal Deger As String)
			Process.ExecSQL("update parametre set ad = @ad where kod = @kod", Deger, Kod)
		End Sub

		Sub ParametreSil(ByVal Process As Token, ByVal Kod As Integer)
			Process.ExecSQL("delete from parametre where kod = @kod", Kod)
		End Sub

		Function PEKOrtalama(Token As Token, yasa As String, t1 As Date, t2 As Date, t3 As Date) As Recordset
			Select Case yasa
				Case "SGY"
					Q("select bordrotar,count(*) as adet,sum(pek) as pek,sum(pogs) as pogs,sum(pek)/sum(pogs)*30 as ortpek from")
					Q("(select bordrotar,sno,max(pogs) as pogs,max(pek) as pek from shesk")
					Q("where bordrotar in (@d1,@d2,@d3) and len(bordro)=2 and bordro not in ('L1','L','X1','X2','Y1') group by bordrotar,sno) t group by bordrotar order by bordrotar")
				Case "SSY"
					Q("select bordrotar,count(*) as adet,sum(pek) as pek,sum(pogs) as pogs,sum(pek)/sum(pogs)*30 as ortpek from")
					Q("(select bordrotar,sno,max(pogs) as pogs,max(pek) as pek from shesk")
					Q("where bordrotar in (@d1,@d2,@d3) and len(bordro)=1 and bordro not in ('L','X','Y') group by bordrotar,sno) t group by bordrotar order by bordrotar")
			End Select
			Return Token.OpenSQL("*", t1, t2, t3)
		End Function

	End Module

End Namespace


